
import sqlite3

#检查是否存在该班级
def find_class_name(class_name):
    import sqlite3
    # 创建数据库连接
    conn = sqlite3.connect('tables.db')

    # 创建游标
    cursor = conn.cursor()

    cursor.execute("SELECT COUNT(*) FROM class WHERE class_name = ?", (class_name,))
    result = cursor.fetchone()
    if result[0] > 0:
        #print('1')
        return True  # 存在该班级
    else:
        #print('2')
        return False  # 不存在该班级
    # 关闭游标和连接
    cursor.close()
    conn.close()

#查找该老师创建的所有班级
def teacher_all_class(job_id):
    import sqlite3
    # 创建数据库连接
    conn = sqlite3.connect('tables.db')

    # 创建游标
    cursor = conn.cursor()

    # 查询老师创建的所有班级
    cursor.execute("SELECT * FROM class WHERE teacher_name = (SELECT teacher_name FROM teachers WHERE job_id = ?)",
                   (job_id,))

    # 获取查询结果
    classes = cursor.fetchall()
    print(classes)
    # 关闭游标和连接
    cursor.close()
    conn.close()

#创建班级
def teacher_crete_class(job_id,class_name):
    import sqlite3
    # 创建数据库连接
    conn = sqlite3.connect('tables.db')

    # 创建游标
    cursor = conn.cursor()

    # 如果老师不存在，添加到班级表

    cursor.execute("INSERT INTO class (class_name, job_id) VALUES (?, ?)", (class_name, job_id))
    conn.commit()
    #print("老师已创建到班级")
    cursor.execute("UPDATE teachers SET class_all = ? WHERE job_id = ?", (class_name, job_id))
    conn.commit()
        # 关闭游标和连接
    cursor.close()
    conn.close()





#添加学生到班级
def add_student_to_class(student_id, class_id):
    # 连接到数据库
    conn = sqlite3.connect('tables.db')
    cursor = conn.cursor()

    # 向student_classes表中插入记录
    cursor.execute("INSERT INTO student_classes (student_id, class_id) VALUES (?, ?)", (student_id, class_id))

    # 提交更改并关闭连接
    conn.commit()
    conn.close()

#删除学生
def remove_student_from_class(student_id, class_id):
    # 连接到数据库
    conn = sqlite3.connect('tables.db')
    cursor = conn.cursor()

    # 从student_classes表中删除记录
    cursor.execute("DELETE FROM student_classes WHERE student_id = ? AND class_id = ?", (student_id, class_id))

    # 提交更改并关闭连接
    conn.commit()
    conn.close()

#修改班级学生
def update_student_class(student_id, new_class_id):
    # 连接到数据库
    conn = sqlite3.connect('tables.db')
    cursor = conn.cursor()

    # 更新student_classes表中的记录
    cursor.execute("UPDATE student_classes SET class_id = ? WHERE student_id = ?", (new_class_id, student_id))

    # 提交更改并关闭连接
    conn.commit()
    conn.close()

#查询指定班级的学生列表
def get_students_in_class(class_id):
    # 连接到数据库
    conn = sqlite3.connect('tables.db')
    cursor = conn.cursor()

    # 查询指定班级的学生列表
    cursor.execute(
        "SELECT students.student_id, students.name FROM students INNER JOIN student_classes ON students.student_id = student_classes.student_id WHERE student_classes.class_id = ?",
        (class_id,))
    students = cursor.fetchall()
    print(f"Found {len(students)} students in class {class_id}")
    # 关闭连接并返回学生列表
    conn.close()
    return students
#查询指定学生加入的所有班级
def get_students_to_class(stu_id):
    # 连接到数据库
    conn = sqlite3.connect('tables.db')
    cursor = conn.cursor()

    # 查询指定学生加入的所有班级
    cursor.execute(
        "SELECT student_classes.class_id FROM student_classes INNER JOIN students ON students.student_id = student_classes.student_id WHERE students.student_id = ?",
        (stu_id,)
    )
    classes = cursor.fetchall()

    # 关闭连接并返回班级列表
    conn.close()

    return classes